#! /usr/bin/perl

package DBQueries ;

use strict ;
use DBI ;

$|=1 ;

my $dbh = DBI->connect( "dbi:SQLite:database.db" ) || die "Cannot connect: $DBI::errstr" ;

#sub new
#{
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `Users` (
	  `UserID` VARCHAR(45) NOT NULL PRIMARY KEY UNIQUE ,
	  `First_Name` VARCHAR(45) NOT NULL ,
	  `Last_Name` VARCHAR(45) NOT NULL ,
	  `Email` VARCHAR(45) NOT NULL ,
	  `Password` VARCHAR(45) NOT NULL )" ) ;
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `Files` (
	  `FileID` INTEGER PRIMARY KEY ,
	  `UserID` VARCHAR(45) NOT NULL ,
	  `File_Name` VARCHAR(45) NOT NULL ,
	  `File` BLOB NULL ,
	  CONSTRAINT `fk_Files_Users1`
	    FOREIGN KEY (`UserID` )
	    REFERENCES `Users` (`UserID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE)" ) ;
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `Groups` (
	  `GroupID` VARCHAR(45) PRIMARY KEY ,
	  `UserID` VARCHAR(45) NOT NULL ,
	  CONSTRAINT `fk_Groups_Users1`
	    FOREIGN KEY (`UserID` )
	    REFERENCES `Users` (`UserID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE)" ) ;
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `Group_Files` (
	  `GroupID` VARCHAR(45) NOT NULL ,
	  `FileID` INT NOT NULL ,
	  PRIMARY KEY (`FileID`, `GroupID`) ,
	  CONSTRAINT `fk_Group_Files_Files1`
	    FOREIGN KEY (`FileID` )
	    REFERENCES `Files` (`FileID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE,
	  CONSTRAINT `fk_Group_Files_Groups1`
	    FOREIGN KEY (`GroupID` )
	    REFERENCES `Groups` (`GroupID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE)" ) ;
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `User_Groups` (
	  `UserID` VARCHAR(45) NOT NULL ,
	  `GroupID` VARCHAR(45) NOT NULL ,
	  PRIMARY KEY (`GroupID`, `UserID`) ,
	  CONSTRAINT `fk_User_Groups_Groups1`
	    FOREIGN KEY (`GroupID` )
	    REFERENCES `Groups` (`GroupID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE,
	  CONSTRAINT `fk_User_Groups_Users1`
	    FOREIGN KEY (`UserID` )
	    REFERENCES `Users` (`UserID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE)" ) ;
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `User_Files` (
	  `UserID` VARCHAR(45) NOT NULL ,
	  `FileID` INT NOT NULL ,
	  PRIMARY KEY (`UserID`, `FileID`) ,
	  CONSTRAINT `fk_User_Files_Users`
	    FOREIGN KEY (`UserID` )
	    REFERENCES `Users` (`UserID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE,
	  CONSTRAINT `fk_User_Files_Files1`
	    FOREIGN KEY (`FileID` )
	    REFERENCES `Files` (`FileID` )
	    ON DELETE CASCADE
	    ON UPDATE CASCADE)" ) ;
	    
	
	$dbh->do( "CREATE  TABLE IF NOT EXISTS `Info` (
  `InfoID` INT PRIMARY KEY ,
  `UserID` VARCHAR(45) NULL ,
  `GroupID` VARCHAR(45) NULL ,
  `FileID` INT NULL ,
  `Info_Type` VARCHAR(45) NULL ,
  `Info` VARCHAR(45) NULL ,
  `Value` INT NULL ,
  CONSTRAINT `fk_Info_Files1`
    FOREIGN KEY (`FileID` )
    REFERENCES `Files` (`FileID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Info_Groups1`
    FOREIGN KEY (`GroupID` )
    REFERENCES `Groups` (`GroupID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Info_Users1`
    FOREIGN KEY (`UserID` )
    REFERENCES `Users` (`UserID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)" ) ;

#}

# add file to db
# parameters UserID, File_Name
# return 1 if added successfully, else return 0 if file already exists
sub addFile
{
	if( !isFile( @_ ) )
	{
		$dbh->do( "INSERT INTO Files ( UserID, File_Name )
					VALUES ( '$_[0]', '$_[1]' )" ) ;
		return 1 ;
	}
	return 0 ;
}

# add info about an ID
# parameters UserID | UserID, GroupID | FileID, Info_Type, Info, Value
# return 1 if success, 0 if info already exists
sub addInfo
{
	my $value = 0 ;
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		if( defined( $_[4] ) )
		{
			$value = $_[4] ;
		}
		$dbh->do( "INSERT INTO Info ( UserID, GroupID, Info_Type, Info, Value )
					VALUES ( '$_[0]', '$_[1]', '$_[2]', '$_[3]', '$value' )" ) ;
		return 1 ;
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		if( defined( $_[3] ) )
		{
			$value = $_[3] ;
		}
		$dbh->do( "INSERT INTO Info ( UserID, Info_Type, Info, Value )
					VALUES ( '$_[0]', '$_[1]', '$_[2]', '$value' )" ) ;
		return 1 ;
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		if( defined( $_[3] ) )
		{
			$value = $_[3] ;
		}
		$dbh->do( "INSERT INTO Info ( FileID, Info_Type, Info, Value )
					VALUES ( '$_[0]', '$_[1]', '$_[2]', '$value' )" ) ;
		return 1 ;
	}
	return 0 ;
}

# add a file to a group
# parameters GroupID, FileID
# return 1 if success, 0 if group doesn't exist
sub addFileToGroup
{
	if( !isFileInGroup( @_ ) )	# make sure that file isn't already in group
	{
		$dbh->do( "INSERT INTO Group_Files ( GroupID, FileID )
					VALUES ( '$_[0]', '$_[1]' )" ) ;
		return 1 ;
	}
	return 0 ;
}

# add group to db
# parameters UserID, GroupID
# return 1 if successfully added group, 0 if either user doesn't exist or group already exists
sub addGroup
{
	if( isUser( $_[0] ) == 1 && isGroup( $_[1] ) == 0 )	# make sure that user exists and groupID isn't used
	{
		$dbh->do( "INSERT INTO Groups ( UserID, GroupID )
					VALUES ( '$_[0]', '$_[1]' )" ) ;		# insert new group to groups table
		return 1 ;
	}
	return 0 ;
}

# add user to database
# parameters UserID, First name, last name, email, password
# return 1 if added successfully, else return 0 if user already existed or not enough arguments
sub addUser
{
	if( isUser( $_[0] ) == 0 && scalar( @_ ) >= 5 )
	{
		$dbh->do( "INSERT INTO Users ( UserID, First_Name, Last_Name, Email, Password )
					VALUES ( '$_[0]', '$_[1]', '$_[2]', '$_[3]', '$_[4]' )" ) ;
		return 1 ;
	}
	return 0 ;
}


# add user to an existing group
# parameters UserID, GroupID
# return 1 if added user, return 0 if user or group doesn't exist
sub addUserToGroup
{
	if( isUser( $_[0] ) == 1 && isGroup( $_[1] ) == 1 )	# make sure that user and group exist
	{
		$dbh->do( "INSERT INTO User_Groups ( UserID, GroupID )
					VALUES ( '$_[0]', '$_[1]' )" ) ;
		return 1 ;
	}
	return 0 ;
}

# test if user is in database
# parameters UserID
# return 1 if exists, else return 0
sub isUser
{
	my $sql = qq/SELECT *
						FROM Users
						WHERE UserID = '$_[0]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;		# get the result
	
	return scalar( @row ) > 0 ? 1 : 0 ;		
}

# get the user's password
# parameters UserID
# return user password, else return 0 if error
sub getUserPassword
{
	my $sql = qq/SELECT *
						FROM Users
						WHERE UserID = '$_[0]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;		# get the result
	
	return scalar( @row ) > 0 ? $row[4] : 0 ;
}

# get an array of the user info
# parameters UserID
# return array of user info if exists, else return 0
sub getUserInfo
{
	my $sql = qq/SELECT First_Name, Last_Name, Email
						FROM Users
						WHERE UserID = '$_[0]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;		# get the result
	
	return scalar( @row ) > 0 ? @row : 0 ;
}

# get the UserID of the owner of the group
# parameters GroupID
# return UserID, or 0 if no such group
sub getGroupOwner
{
	my $sql = qq/SELECT UserID
				FROM Groups
				WHERE GroupID = '$_[0]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;
	
	return scalar( @row ) > 0 ? $row[0] : 0 ;
}

# get an array with all the info of a certain type on an ID
# parameters UserID | UserID, GroupID | FileID, Type
# return an array of Info, else return 0 if no such ID
sub getInfoType
{
	my $sql ;
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		$sql = qq/SELECT Info
				FROM Info
				WHERE UserID = '$_[0]' AND GroupID = '$_[1]' AND Info_Type = '$_[2]'
				ORDER BY Info/ ;			# create the query
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		$sql = qq/SELECT Info
				FROM Info
				WHERE UserID = '$_[0]' AND Info_Type = '$_[1]'
				ORDER BY Info/ ;			# create the query
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		$sql = qq/SELECT Info
				FROM Info
				WHERE FileID = '$_[0]' AND Info_Type = '$_[1]'
				ORDER BY Info/ ;			# create the query
	}
	else
	{
		return 0 ;
	}
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array with all the info of a certain type on an ID
# parameters UserID | UserID, GroupID | FileID, Type
# return an array of Info and value seperated by a delimiter
sub getInfoTypeValue
{
	my $sql ;
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		$sql = qq/SELECT Info, Value
				FROM Info
				WHERE UserID = '$_[0]' AND GroupID = '$_[1]' AND Info_Type = '$_[2]'
				ORDER BY Value DESC/ ;			# create the query
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		$sql = qq/SELECT Info, Value
				FROM Info
				WHERE UserID = '$_[0]' AND Info_Type = '$_[1]'
				ORDER BY Value DESC/ ;			# create the query
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		$sql = qq/SELECT Info, Value
				FROM Info
				WHERE FileID = '$_[0]' AND Info_Type = '$_[1]'
				ORDER BY Value DESC/ ;			# create the query
	}
	else
	{
		return 0 ;
	}	
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array with all the info of a certain type on an ID, where the the value follows the constraint of the operator
# parameters UserID, Info_Type, Info, Operator, Value 
# return an array of FileID's, File_Name's, Values separated by a delimiter .|><|.
sub getInfoTypeValueConstraint
{

	my $sql = qq/SELECT DISTINCT Files.FileID
				FROM Info, Files, Group_Files, User_Groups
				WHERE Info.FileID IS NOT NULL AND
					Info.Info_Type = '$_[1]' AND 
					Info.Info = '$_[2]' AND
					Info.Value $_[3] '$_[4]' AND
					Info.FileID = Files.FileID AND
					( Files.UserID = '$_[0]' OR
					( User_Groups.GroupID = Group_Files.GroupID AND
						Files.FileID = Group_Files.FileID AND
						User_Groups.UserID = '$_[0]' ) )/ ;			# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# remove a user from database
# parameters userID
# return 1 if deleted corructly, or 0 if no such user
sub removeUser
{
	if( isUser( $_[0] ) )
	{
		$dbh->do( "DELETE FROM Users 
					WHERE UserID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# removes all users from Users table
# return 1 if deleted successfully, else return 0 (always returns 1)
sub removeAllUsers
{
	$dbh->do( "DELETE FROM Users" ) ;
	return 1 ;
}

# test if user login is correct
# parameters UserID and Password
# return 1 if login successful, else return 0
sub loginUser
{
	my $sql = qq/SELECT *
						FROM Users
						WHERE UserID = '$_[0]' and Password = '$_[1]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;		# get the result
	return scalar( @row ) > 0 ? 1 : 0 ;		# if there are any results then the username+password are valid and return 1, else return 0
}

# get an array of all the files a user has uploaded
# parameters UserID, (optional) GroupID
# return array of file names, or 0 if no such files exists
sub getUserFileNames
{
	my $sql ;
	if( scalar( @_ ) > 1 )	# if we have two inputs then we want all files excluding files added to group
	{
		$sql = qq/SELECT File_Name
					FROM Files
					WHERE UserID = '$_[0]' AND File_Name NOT IN
						(SELECT File_Name
						FROM Group_Files, Files
						WHERE Group_Files.FileID = Files.FileID 
							AND Group_Files.GroupID = '$_[1]')/ ;		# create the query
	}
	else		# else just get files uploaded by user
	{
		$sql = qq/SELECT File_Name
				FROM Files
				WHERE UserID = '$_[0]'
				ORDER BY File_Name/ ;		# create the query
	}
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# remove all the files that belong to a user from the db
# parameters UserID
# return 1 if successful, else return 0 if no such UserID
sub removeAllUserFiles
{
	if( isUser( $_[0] ) )
	{
		$dbh->do( "DELETE FROM Files 
					WHERE UserID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# test if file is in database
# parameters UserID, File_Name
# return 1 if exists, else return 0
sub isFile
{
	my $sql ;
	if( scalar( @_ ) > 1 )
	{
		$sql = qq/SELECT *
					FROM Files
					WHERE UserID = '$_[0]' AND File_Name = '$_[1]'/ ;	# create the query
	}
	else
	{
		$sql = qq/SELECT *
					FROM Files
					WHERE FileID = '$_[0]'/ ;	# create the query
	}
	return isTest( $sql ) ;	
}

# remove a file from db
# parameters FileID
# return 1 if removed successfully, else return 0 if file doesn't exist
sub removeFile
{
	
	$dbh->do( "DELETE FROM Files
				WHERE FileID = '$_[0]'" ) ;
	return 1 ;
}

# get the name of file, given its FileID
# parameters UserID
# return file name, or 0 if no such file exists
sub getFileName
{
	my $sql = qq/SELECT *
				FROM Files
				WHERE FileID = '$_[0]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;
	return scalar( @row ) > 0 ? $row[2] : 0 ;
}

# check if group exists
# parameters GroupID
# return 1 if group exists, 0 if no such group
sub isGroup
{
	my $sql = qq/SELECT *
						FROM Groups
						WHERE GroupID = '$_[0]'/ ;	# create the query
	return isTest( $sql ) ;	
}

# check if user created group
# parameters UserID, GroupID
# return 1 if user created group, else return 0
sub isGroupOwner
{
	my $sql = qq/SELECT *
						FROM Groups
						WHERE UserID = '$_[0]' AND GroupID ='$_[1]'/ ;	# create the query
	return isTest( $sql ) ;	
}

# remove group from both the Groups table and the User_Groups table
# parameters GroupID
# return 1 if removed successfully, 0 if no such group exists
sub removeGroup
{
	if( isGroup( $_[0] ) )
	{
		$dbh->do( "DELETE FROM Groups 
					WHERE GroupID = '$_[0]'" ) ;
		$dbh->do( "DELETE FROM User_Groups 
					WHERE GroupID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# remove a member from the group
# parameters UserID, GroupID
# return 1 on success, 0 if no such group
sub removeGroupMember
{
	if( isGroup( $_[1] ) )
	{
		$dbh->do( "DELETE FROM User_Groups 
					WHERE UserID = '$_[0]' AND GroupID = '$_[1]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# get an array with all the members of a group
# parameters GroupID
# return an array of the group members, or 0 if no members exist
sub getGroupMembers
{
	my $sql = qq/SELECT UserID
				FROM User_Groups
				WHERE GroupID = '$_[0]'
				ORDER BY UserID/ ;	# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array of all the group names a user is part of
# parameters UserID
# return array of group names, or 0 if no groups found
sub getUserGroups
{
	my $sql = qq/SELECT GroupID
				FROM User_Groups
				WHERE UserID = '$_[0]'
				ORDER BY GroupID/ ;	# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array of the groups the user created
# parameters UserID
# return an array GroupID's
sub getUserCreatedGroups
{
	my $sql = qq/SELECT GroupID
				FROM Groups
				WHERE UserID = '$_[0]'
				ORDER BY GroupID/ ;	# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array of all file names associated with group
# parameters GroupID
# return an array of file names
sub getGroupFileNames
{
	my $sql = qq/SELECT File_Name
				FROM Group_Files, Files
				WHERE Group_Files.FileID = Files.FileID AND 
				Group_Files.GroupID = '$_[0]'
				ORDER BY File_Name/ ;	# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# check if file is in the group
# parameters GroupID, FileID
# return 1 if file exists in group, 0 if not
sub isFileInGroup
{
	my $sql = qq/SELECT *
						FROM Group_Files
						WHERE GroupID = '$_[0]' AND FileID = '$_[1]'/ ;	# create the query
	return isTest( $sql ) ;	
}

# check if UserID is owner of FileID
# parameters UserID, FileID
# return 1 if user is owner, else return 0
sub isFileOwner
{
	my $sql = qq/SELECT *
						FROM Files
						WHERE UserID = '$_[0]' AND FileID = '$_[1]'/ ;	# create the query
	return isTest( $sql ) ;	
}

# remove a file from a group
# parameters GroupID, FileID
# return 1 if success, else 0 if file not in group
sub removeFileFromGroup
{
	if( isFileInGroup( @_ ) )
	{
		$dbh->do( "DELETE FROM Group_Files
					WHERE GroupID = '$_[0]' AND FileID = '$_[1]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# get a list of all file names that a user has permision for
# parameters UserID
# return an array of all files names that a user has access to
sub getAllUserFileNames
{
	my $sql = qq/SELECT File_Name
				FROM Group_Files, Files, User_Groups
				WHERE
				User_Groups.GroupID = Group_Files.GroupID AND
				Files.FileID = Group_Files.FileID AND
				User_Groups.UserID = '$_[0]'
				ORDER BY File_Name/ ;	# create the query
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# get an array of all user names (if parameter is passed then it excludes users of that group)
# parameters (optional) GroupID
# return an array of user names
sub getAllUsers
{
	my $sql ;
	if( scalar( @_ ) > 0 )	# if we have an input then we want all users excluding group users
	{
		$sql = qq/SELECT UserID
					FROM Users
					WHERE UserID NOT IN
					(SELECT UserID
					FROM User_Groups
					WHERE GroupID = '$_[0]' UNION
					SELECT UserID
					FROM Groups
					WHERE GroupID = '$_[0]')
					ORDER BY UserID/ ;		# create the query
	}
	else		# else just get all users
	{
		$sql = qq/SELECT UserID
				FROM Users
				ORDER BY UserID/ ;			# create the query
	}
	
	return getArrayOf( $sql ) ;				# get array of whatever it is the query is requesting
}

# pass the query, and return an array of the values from the query
# parameters sql query
# return an array of values
sub getArrayOf
{
	#print "in getArrayOf: " . join( ", ", @_ ) . "\n" ;
	my $sth = $dbh->prepare( $_[0] ) ;		# prepare it
	$sth->execute() ;						# execute the query
	
	my @stuff ;
	my $i = 0 ;
	while ( my @row = $sth->fetchrow_array )		# dump all the group names into an array
	{
		$stuff[$i] = join( ".|><|.", @row ) ;
		$i++ ;
	}
	
	return scalar( @stuff ) > 0 ? @stuff : 0 ;	# return the array with all the file names
}

# get the info about a file, specifically the FileID
# parameters UserID, File_Name
# return file ID, or 0 if no such file exists
sub getFileID
{
	my $sql = qq/SELECT FileID
				FROM Files
				WHERE UserID = '$_[0]' AND File_Name = '$_[1]'/ ;	# create the query
	my $sth = $dbh->prepare( $sql ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;
	
	return scalar( @row ) > 0 ? $row[0] : 0 ;
}

# pass an sql query and return a true or false for it
# parameters sql query
# return 1 or 0 for true or false
sub isTest
{
	my $sth = $dbh->prepare( $_[0] ) ;		# prepare it
	$sth->execute() ;						# execute the query
	my @row = $sth->fetchrow_array ;		# get the result
	
	return scalar( @row ) > 0 ? 1 : 0 ;	
}

# remove a specific info value about an ID
# parameters UserID | UserID, GroupID | FileID, Info_Type, Info
# return 1 if success, 0 if info doesn't exists
sub removeInfo
{
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID = '$_[1]' AND FileID IS NULL AND Info_Type = '$_[2]' AND Info = '$_[3]'" ) ;
		return 1 ;
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID IS NULL AND FileID IS NULL AND Info_Type = '$_[1]' AND Info = '$_[2]'" ) ;
		return 1 ;
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE FileID = '$_[0]' AND UserID IS NULL AND GroupID IS NULL AND Info_Type = '$_[1]' AND Info = '$_[2]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# remove all info values about an ID of Info_Type
# parameters UserID | UserID, GroupID | FileID, Info_Type
# return 1 if success, 0 if info doesn't exists
sub removeInfoType
{
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID = '$_[1]' AND FileID IS NULL AND Info_Type = '$_[2]'" ) ;
		return 1 ;
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID IS NULL AND FileID IS NULL AND Info_Type = '$_[1]'" ) ;
		return 1 ;
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE FileID = '$_[0]' AND UserID IS NULL AND GroupID IS NULL AND Info_Type = '$_[1]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# remove all info values about an ID
# parameters UserID | UserID, GroupID | FileID
# return 1 if success, 0 if info doesn't exists
sub removeInfoID
{
	if( isUser( $_[0] ) && isGroup( $_[1] ) )	# make sure that group exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID = '$_[1]' AND FileID IS NULL" ) ;
		return 1 ;
	}
	elsif( isUser( $_[0] ) )	# make sure that user exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE UserID = '$_[0]' AND GroupID IS NULL AND FileID IS NULL" ) ;
		return 1 ;
	}
	elsif( isFile( $_[0] ) )	# make sure that file exists
	{
		$dbh->do( "DELETE FROM Info
					WHERE FileID = '$_[0]' AND UserID IS NULL AND GroupID IS NULL" ) ;
		return 1 ;
	}
	return 0 ;
}

# update the file name
# parameters FileID
# return 1 if success, 0 if no such file
sub updateFileName
{
	if( isFile( $_[0] ) )
	{
		$dbh->do( "UPDATE Files
					SET File_Name = '$_[1]'
					WHERE FileID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# update the user info
# parameters UserID, first name, last name, email, password
# return 1 if successful, else return 0 if no such user or if insufficient values
sub updateUserInfo
{
	if( isUser( $_[0] ) )
	{
		$dbh->do( "UPDATE Users
					SET First_Name = '$_[1]', Last_Name = '$_[2]', Email = '$_[3]'
					WHERE UserID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

# update the user password
# parameters UserID, Password
# return 1 if successful, else return 0 if no such user or if insufficient values
sub updateUserPassword
{
	if( isUser( $_[0] ) )
	{
		$dbh->do( "UPDATE Users
					SET Password = '$_[1]'
					WHERE UserID = '$_[0]'" ) ;
		return 1 ;
	}
	return 0 ;
}

1 ;